package dao;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import model.Employee;
import model.Project;
import model.Title;



public class EmployeeDAO {
	ConnectionDB con;
	ObjectDAO objectDao;
	
	public EmployeeDAO() throws SQLException, ClassNotFoundException{
		con = new ConnectionDB();
		
		objectDao = new ObjectDAO();
	}
	
	/**
	 * check the login process - username and password are matched with a user in db
	 * @param username
	 * @param password
	 * @return db id of user/employee 
	 * @throws SQLException
	 */
	@SuppressWarnings("static-access")
	public int checkLogin(String username, String password) throws SQLException {
		con = new ConnectionDB();
		int a = 0;
		Employee employee = null;
		PreparedStatement ps;
		ResultSet rs;
		
		try {
			String query = "SELECT * FROM Employee WHERE username = ? AND password = ?";
			
            ps = con.getConnection().prepareStatement(query);
            ps.setString(1, username);
            ps.setString(2, password);
            rs = ps.executeQuery();
            

            if (rs.next()) {
            	int id = rs.getInt(1);
            	rs.close();
                ps.close();
                return id;
            } 
            else {
            	rs.close();
                ps.close();
            	return 0;
            }
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			ConnectionDB.closeConnection();
		}
		
		return 0; 
	}
	
	
	/**
	 * get a specific Employee
	 * @param userId
	 * @return employee
	 * @throws SQLException
	 */
	@SuppressWarnings("static-access")
	public Employee getAEmployee(int userId) throws SQLException {
		con = new ConnectionDB();
		Employee employee = null;;
		PreparedStatement ps;
		ResultSet rs;
		
		try {
			String query = "SELECT * FROM Employee WHERE userId = ?";
			
            ps = con.getConnection().prepareStatement(query);
            ps.setInt(1, userId);
            rs = ps.executeQuery();
            

            if (rs.next()) {
            	Title title = objectDao.getATitle(rs.getInt(6));
                
                Project proj;
                if(rs.getInt(7) != 0) {
                	proj = objectDao.getAProject(rs.getInt(7));
                } else {
                	proj = new Project(0, "No Project now", ""); 
                }
                
                employee = new Employee(rs.getInt(1), rs.getString(2), rs.getString(3), rs.getString(4), rs.getString(5),
                		title, proj, rs.getString(8));
            }
            rs.close();
            ps.close();
			return employee;	
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			ConnectionDB.closeConnection();
		}
		return employee;
	}
	
	
	/**
	 * get all users/employees from db
	 * @return list of user
	 * @throws SQLException
	 */
	public ArrayList<Employee> getListEmployees() throws SQLException {
		con = new ConnectionDB();
		ArrayList<Employee> list = new ArrayList<Employee>();
		Statement st;
		ResultSet rs;
		
		try {
			String query = "SELECT * FROM Employee";
			
            st = ConnectionDB.getConnection().createStatement();
            rs = st.executeQuery(query);
            while (rs.next()) {
            	Title title = objectDao.getATitle(rs.getInt(6));
                Project proj;
                
                if(rs.getInt(7) != 0) {
                	proj = objectDao.getAProject(rs.getInt(7));
                } else {
                	proj = new Project(0, "No Project now", ""); 
                }
                
                Employee employee = new Employee(rs.getInt(1), rs.getString(2), rs.getString(3), rs.getString(4), rs.getString(5),
                		title, proj, rs.getString(8));
            	
            	list.add(employee);
            }
			
            rs.close();
            st.close();
			return list;	
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			ConnectionDB.closeConnection();
		}
		return list;
	}
}
